﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Cocbook_DTO;

namespace Cocbook_DAL
{
    public class ProjectDAL
    {
        public bool CreateProject(Project p)
        {
            DB.SetQuery("INSERT INTO Project VALUES(@name,@address,@sdate,@edate,@table,@status)");
            DB.AddParameters("name", p.ProjectName);
            DB.AddParameters("address", p.ProjectAddress);
            DB.AddParameters("sdate", p.StartDate);
            DB.AddParameters("edate", p.EndDate);
            DB.AddParameters("table", p.Table);
            DB.AddParameters("status", p.Status);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.CreateProject", e.ToString());
                return false;
            }
        }
        public bool DeleteProject(int id)
        {
            DB.SetQuery("DELETE FROM Project WHERE ProjectID = @id");
            DB.AddParameters("id", id);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.DeleteProject", e.ToString());
                return false;
            }
        }
        public bool UpdateProject(Project p)
        {
            DB.SetQuery("UPDATE Project SET ProjectName = @name, ProjectAddress = @address, StartDate = @sdate, EndDate = @edate, Status = @status"+
                         " WHERE ProjectID = @id");
            DB.AddParameters("id",p.ProjectID);
            DB.AddParameters("name", p.ProjectName);
            DB.AddParameters("address", p.ProjectAddress);
            DB.AddParameters("sdate", p.StartDate);
            DB.AddParameters("edate", p.EndDate);
            DB.AddParameters("status", p.Status);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.UpdateProject", e.ToString());
                return false;
            }
        }
        public Project GetProjectByID(int id)
        {
            try
            {
                DB.SetQuery("SELECT ProjectID, ProjectName, ProjectAddress, StartDate, EndDate, [Table], Status FROM Project WHERE ProjectID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Project p = new Project();
                if (llo.Count > 0)
                {
                    p.ProjectID = Convert.ToInt16(llo[0][0]);
                    p.ProjectName = Convert.ToString(llo[0][1]);
                    p.ProjectAddress = Convert.ToString(llo[0][2]);
                    p.StartDate = Convert.ToDateTime(llo[0][3]);
                    p.EndDate = Convert.ToDateTime(llo[0][4]);
                    p.Table = Convert.ToInt16(llo[0][5]);
                    p.Status = Convert.ToBoolean(llo[0][6]);
                }
                else
                {
                    "".Substring(2);
                }
                return p;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.GetProjectByID", e.ToString());
                return null;
            }
        }

        public Int32 GetQuatityOfDealByProjectID(int pid)
        {
            try
            {
                DB.SetQuery(@"SELECT (oSold.OC+bSold.BC) AS Deal
                            FROM
	                            (SELECT COUNT(o.OrderID) AS OC FROM [Order] o, Project p WHERE o.ProjectID = p.ProjectID AND p.ProjectID = @pid) AS oSold,
	                            (SELECT COUNT(b.BillID) AS BC FROM Bill b, Project p WHERE b.ProjectID = p.ProjectID AND p.ProjectID = @pid) AS bSold");
                DB.AddParameters("pid", pid);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Int32 result = 0;
                result = Convert.ToInt32(llo[0][0]);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.GetQuatityOfDealByProjectID", e.ToString());
                return 0;
            }
        }

        public List<WareHouseView> GetWareHouseViewByProjectID(int pid = 0, bool top = false)
        {
            try
            {
                string sqlString = @" dbo.Book.ISBN, dbo.Book.Title, dbo.Project_Book.Copy, ISNULL(SUM(dbo.Bill_Book.Copy), 0) AS Sold, 
                                                                 dbo.Project_Book.Copy - ISNULL(SUM(dbo.Bill_Book.Copy), 0) AS Inventory
                                        FROM            dbo.Bill_Book RIGHT OUTER JOIN
                                                                 dbo.Book ON dbo.Bill_Book.BookID = dbo.Book.BookID LEFT OUTER JOIN
                                                                 dbo.Project_Book INNER JOIN
                                                                 dbo.Project ON dbo.Project_Book.ProjectID = dbo.Project.ProjectID ON dbo.Book.BookID = dbo.Project_Book.BookID
                                        WHERE        (dbo.Project.ProjectID = @pid)
                                        GROUP BY dbo.Book.ISBN, dbo.Book.Title, dbo.Project_Book.Copy
                                        ORDER BY Sold DESC, Inventory";
                if (top)
                { 
                    sqlString = @"SELECT        TOP (100) "+sqlString;
                }
                else
                {
                    sqlString = @"SELECT "+sqlString;
                }
                DB.SetQuery(sqlString);
                DB.AddParameters("pid", pid);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<WareHouseView> result = new List<WareHouseView>();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        WareHouseView whv = new WareHouseView();
                        whv.ISBN = Convert.ToInt64(lo[0]);
                        whv.Title = Convert.ToString(lo[1]);
                        whv.Import = Convert.ToInt16(lo[2]);
                        whv.Sold = Convert.ToInt16(lo[3]);
                        whv.Inventory = Convert.ToInt16(lo[4]);
                        result.Add(whv);
                    }
                }
                else "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.GetWareHouseViewByProjectID", e.ToString());
                return null;
            }
        }

        public Project GetProjectByStatus()
        {
            try
            {
                DB.SetQuery("SELECT ProjectID, ProjectName, ProjectAddress, StartDate, EndDate, [Table], Status FROM Project WHERE Status = 'true';");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Project p = new Project();
                if (llo.Count > 0)
                {
                    p.ProjectID = Convert.ToInt16(llo[0][0]);
                    p.ProjectName = Convert.ToString(llo[0][1]);
                    p.ProjectAddress = Convert.ToString(llo[0][2]);
                    p.StartDate = Convert.ToDateTime(llo[0][3]);
                    p.EndDate = Convert.ToDateTime(llo[0][4]);
                    p.Table = Convert.ToInt16(llo[0][5]);
                    p.Status = Convert.ToBoolean(llo[0][6]);
                }
                else
                {
                    "".Substring(2);
                }
                return p;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.GetProjectByStatus", e.ToString());
                return null;
            }
        }


        public List<Project> GetAllProject()
        {
            try
            {
                DB.SetQuery("SELECT ProjectID, ProjectName, ProjectAddress, StartDate, EndDate, [Table], Status FROM Project");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Project> project = new List<Project>();
                Project p;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        p = new Project();
                        p.ProjectID = Convert.ToInt16(lo[0]);
                        p.ProjectName = Convert.ToString(lo[1]);
                        p.ProjectAddress = Convert.ToString(lo[2]);
                        p.StartDate = Convert.ToDateTime(lo[3]);
                        p.EndDate = Convert.ToDateTime(lo[4]);
                        p.Table = Convert.ToInt32(lo[5]);
                        p.Status = Convert.ToBoolean(lo[6]);
                        project.Add(p);
                    }
                }
                else "".Substring(2);
                return project;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.ProjectDAL.GetAllProject", e.ToString());
                return null;
            }
        }

    }
}
